package Data;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import Util.MiscUtil;

/**
 * General interface to the DB.
 * The class contains static methods for querying and modifying the DB.
 */
public class DbInterface
{
	/**
	 * Traits of the characters which have a one-to-many relationship.
	 * These all have similar table structures.
	 */
	private static String[] O2M = { "power", "species", "universe", "rank", "occupation", "medical_condition" };
	
	/**
	 * Initializes the DB interface.
	 * The method connects to the DB, initialized the connection pool and tests the connection.
	 * 
	 * @param serverAddr The address of the DB server
	 * @param user Username
	 * @param password Password
	 * @return true on success; false otherwise
	 */
	public static boolean initialize(String serverAddr, String user, String password)
	{
		Connection testConnection = null;
		
		try
		{
			ConnectionPool.createInstance(serverAddr, user, password);
			testConnection = ConnectionPool.getConnection();
			
			return true;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return false;
		}
		finally
		{
			if (testConnection != null)
			{
				try
				{
					testConnection.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
					return false;
				}
			}
		}
	}
	
	/**
	 * Gets a DB connection from the connection pool.
	 * 
	 * @return A DB connection
	 * @throws QueryException
	 */
	public static Connection openConnection() throws QueryException 
	{
		Connection conn = null;
		
		try
		{
			conn = ConnectionPool.getConnection();
		}
		catch (SQLException e)
		{
			throw new QueryException(e);
		}
		
		return conn;
	}
	
	/**
	 * Closes the given connection.
	 * 
	 * @throws QueryException 
	 */
	public static void closeConnection(Connection conn) throws QueryException
	{
		try
		{
			conn.close();
		}
		catch (SQLException e)
		{
			throw new QueryException(e);
		}
	}
	
	/**
	 * Retrieves the first ID which corresponds to the given character name.
	 * 
	 * @param name A character name to match
	 * @return The first ID found, or -1 if none found
	 * @throws QueryException
	 */
	public static int getIdFromName(String name) throws QueryException
	{
		int result = -1;
		
		Connection conn = openConnection();
		
		try
		{
			String str = "SELECT id FROM characters WHERE name = ?;";
			
			PreparedStatement pstmt = conn.prepareStatement(str);
			
			pstmt.setString(1, name);			
			
			ResultSet rs = pstmt.executeQuery();
			
			if (rs.first())
				result = rs.getInt("id");
			
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			throw new QueryException(e);
		}
		finally
		{
			closeConnection(conn);
		}
		
		return result;
	}
	
	/**
	 * Retrieves a list of entries which start with the given prefix.
	 * The method supports the fields: characters, power, sepecies, universe, rank, occupation, medical_condition
	 * 
	 * @param attribute The attribute to query. From the list: characters, power, sepecies, universe, rank, occupation, medical_condition
	 * @param prefix The prefix to match
	 * @return A list of entries which match the perfix
	 * @throws QueryException
	 */
	public static List<String> getGeneralAttribute(String attribute, String prefix) throws QueryException
	{
		List<String> result = new ArrayList<String>();
		
		Connection conn = openConnection();
		
		try
		{
			String str = "SELECT name FROM " + attribute + " WHERE name LIKE ?;";
			
			PreparedStatement pstmt = conn.prepareStatement(str);
			
			pstmt.setString(1, prefix + "%");
			
			ResultSet rs = pstmt.executeQuery();
			
			while (rs.next())
				result.add(rs.getString("name"));
			
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			throw new QueryException(e);
		}
		finally
		{
			closeConnection(conn);
		}
		
		return result;
	}
	
	/**
	 * Free-text search for a character.
	 * The method queries the DB and searches in all attribute-tables for the given text.
	 * 
	 * @param query The text to search
	 * @return A list of character IDs who match the query
	 * @throws QueryException
	 */
	public static List<Integer> search(String query) throws QueryException
	{
		List<Integer> result = new ArrayList<Integer>();
		
		Connection conn = openConnection();
		
		try
		{
			String likeStr = "%" + query + "%";
			
			String stmtStr =
				"(SELECT DISTINCT characters.id AS characters_id " +
				"FROM characters " +
				"WHERE characters.name LIKE ?)";
			
			for (int i = 0; i < O2M.length; i++)
			{
				stmtStr +=
					" UNION (SELECT DISTINCT character_" + O2M[i] + ".character_id AS characters_id" +
					" FROM character_" + O2M[i] + ", " + O2M[i] +
					" WHERE character_" + O2M[i] + "." + O2M[i] + "_id = " + O2M[i] + ".id " +
						"AND " + O2M[i] + ".name LIKE ?)";
			}
			
			stmtStr += " LIMIT 1000;";
			
			PreparedStatement pstmt = conn.prepareStatement(stmtStr);
			
			for (int i = 1; i < O2M.length + 2; i++)
				pstmt.setString(i, likeStr);
			
			ResultSet rs = pstmt.executeQuery();
			
			while (rs.next())
				result.add(rs.getInt("characters_id"));
			
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			throw new QueryException(e);
		}
		finally
		{
			closeConnection(conn);
		}
		
		return result;
	}
	
	/**
	 * Searches for characters which match ALL the given search parameters.
	 * 
	 * @param name Text to search in the name
	 * @param gender Text to search in the gender
	 * @param power Text to search in the powers
	 * @param species Text to search in the species
	 * @param universe Text to search in the universes
	 * @param rank Text to search in the ranks
	 * @param occupation Text to search in the occupations
	 * @param medicalCondition Text to search in the medical conditions
	 * @param minHeight Minimum height of the character, in meters
	 * @param maxHeight Maximum height of the character, in meters
	 * @param minWeight Minimum weight of the character, in kilograms
	 * @param maxWeight Maximum weight of the character, in kilograms
	 * @return A list of IDs which match the query
	 * @throws QueryException
	 */
	public static List<Integer> search(String name, String gender, String power,
			String species, String universe, String rank, String occupation, String medicalCondition,
			double minHeight, double maxHeight, double minWeight, double maxWeight) throws QueryException
	{
		List<Integer> result = new ArrayList<Integer>();
		
		String[] queryTable = { power, species, universe, rank, occupation, medicalCondition};
		int bool = 0;
		
		Connection conn = openConnection();
		
		try
		{
			String str = "";
			
			if (name != null)
			{
				str = "SELECT DISTINCT characters.id FROM characters WHERE characters.name LIKE '" + name + "%'";
				bool = 1;
			}
			
			if (gender != null)
			{
				if (bool > 0)
					str += " AND characters.id IN (";
				
				str += "SELECT DISTINCT characters.id " +
						"FROM gender, characters " +
						"WHERE gender.gender LIKE '" + gender + "' " +
							"AND gender.gender_id = characters.gender";
				
				bool++;
			}
			
			if ((minHeight > 0) || (maxHeight > 0))
			{
				if (bool > 0)
					str += " AND characters.id IN (";
				
				str += "SELECT id FROM characters WHERE height >= " + minHeight;
				
				if (maxHeight > 0)
					str += " AND height <= " + maxHeight;
				
				bool++;
			}
			
			if ((minWeight > 0) || (maxWeight > 0))
			{
				if (bool > 0)
					str += " AND characters.id IN (";
				
				str += "SELECT id FROM characters WHERE weight >= " + minWeight;
				
				if (maxHeight > 0)
					str += " AND weight <= " + maxWeight;
				
				bool++;
			}
			
			String stt = " AND characters.id IN (";
			
			for (int i = 0; i < O2M.length; i++)
			{
				if (queryTable[i] != null)
				{
					if (bool > 0)
						str += stt;
					
					str += "SELECT DISTINCT character_" + O2M[i] + ".character_id " +
							"FROM character_" + O2M[i] + ", " + O2M[i] +
							" WHERE character_" + O2M[i] + "." + O2M[i] + "_id = " + O2M[i] + ".id " +
								"AND " + O2M[i] + ".name LIKE '" + queryTable[i] + "%'";
					
					bool++;
					
					stt = " AND character_" + O2M[i] + ".character_id IN (";
				}
			}
			
			for (int i = 0; i < bool - 1; i++)
				str += ")";
			
			str += " LIMIT 1000;";
			
			Statement stmt	= conn.createStatement();
			ResultSet rs = stmt.executeQuery(str);
			
			while (rs.next())
				result.add(rs.getInt(1));
			
			rs.close();
			stmt.close();
		}
		catch (SQLException e)
		{
			throw new QueryException(e);
		}
		finally
		{
			closeConnection(conn);
		}
		
		return result;
	}
	
	/**
	 * Retrieves a list of attribute values for each one of the given IDs, for the given attribute.
	 * 
	 * @param attribute The attribute to retrieve. One of: power, sepecies, universe, rank, occupation, medical_condition
	 * @param ids A list of IDs for which to query the attributes
	 * @return A map (character ID to list) of lists of attribute values
	 * @throws QueryException
	 */
	public static Map<Integer, List<String>> getO2MsfromIDs(String attribute, List<Integer> ids) throws QueryException
	{
		Map<Integer, List<String>> result = new HashMap<Integer, List<String>>();
		
		Connection conn = openConnection();
		
		try
		{
			String idsStr = MiscUtil.aggregateToString(ids);
			
			String str =
				"SELECT " +
					"character_" + attribute + ".character_id AS characters_id, " +
					"GROUP_CONCAT(" + attribute + ".name SEPARATOR ';') AS attribute_names " +
				"FROM " +
					attribute + ", " +
					"character_" + attribute + " " + 
				"WHERE " +
					"character_" + attribute + ".character_id IN (" + idsStr + ") AND " +
					attribute + ".id = character_" + attribute + "." + attribute + "_id " +
				"GROUP BY " +
					"characters_id;";
			
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(str);
			
			while (rs.next())
			{
				int id = rs.getInt("characters_id");
				List<String> names = Arrays.asList(rs.getString("attribute_names").split(";"));
				
				result.put(id, names);
			}
			
			rs.close();
			stmt.close();
		}
		catch (SQLException e)
		{
			throw new QueryException(e);
		}
		finally
		{
			closeConnection(conn);
		}
		
		return result;
	}	
	
	/**
	 * Deletes the character associated with the given ID.
	 * 
	 * @param id The ID of the character to delete
	 * @throws QueryException
	 */
	public static void deleteCharacter(int id) throws QueryException
	{
		Connection conn = openConnection();
		
		try
		{
			String sql = "DELETE FROM characters WHERE characters.id = " + id + ";";
			
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(sql);
			
			stmt.close();
		}
		catch (SQLException e)
		{
			throw new QueryException(e);
		}
		finally
		{
			closeConnection(conn);
		}
	}
	
	/**
	 * Retrieves a character by the given name.
	 * 
	 * @param name The name of the character to retrieve
	 * @return A Character object associated with the name
	 * @throws QueryException
	 */
	public static Character getCharacter(String name) throws QueryException
	{
		return getCharacterFromID(getIdFromName(name));
	}
	
	/**
	 * Retrieves a character by the given ID.
	 * 
	 * @param id The ID of the character to retrieve
	 * @return A Character object associated with the ID
	 * @throws QueryException
	 */
	public static Character getCharacterFromID(int id) throws QueryException
	{
		List<Integer> lst = new ArrayList<Integer>();
		lst.add(id);
		
		return getCharactersFromIDs(lst).get(0);
	}
	
	/**
	 * Retrieves a list of characters by the given IDs.
	 * 
	 * @param ids The IDs of the characters to retrieve
	 * @return A list of Character objects associated with the given IDs
	 * @throws QueryException
	 */
	public static List<Character> getCharactersFromIDs(List<Integer> ids) throws QueryException
	{
		List<Character> chs = null;
		
		Connection conn = openConnection();
		
		try
		{
			String idsStr = MiscUtil.aggregateToString(ids);
			
			String sql =
				"SELECT " +
					"characters.*, " +
					"gender.gender AS gender_name " +
				"FROM " +
					"characters " +
					"LEFT OUTER JOIN " +
						"gender " +
					"ON " +
						"characters.gender = gender.gender_id " +
				"WHERE " +
					"characters.id IN (" + idsStr + ");";
			
			//get Attribute in  character Table
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			
			List<Map<Integer, List<String>>> listO2Ms = new ArrayList<Map<Integer, List<String>>>();
			
			for (int i = 0; i < O2M.length; i++)
				listO2Ms.add(getO2MsfromIDs(O2M[i], ids));
			
			chs = new ArrayList<Character>();
			
			while (rs.next())
			{
				int id = rs.getInt("characters.id");
				String gender = rs.getString("gender_name");
				
				Character ch = new Character(id, rs.getString("name"),
						listO2Ms.get(0).get(id),
						listO2Ms.get(1).get(id), 
						listO2Ms.get(2).get(id),
						listO2Ms.get(3).get(id),
						rs.getFloat("weight"), rs.getFloat("height"),
						rs.getString("photo_url"), gender,
						listO2Ms.get(5).get(id),
						listO2Ms.get(4).get(id));
				
				chs.add(ch);
			}
			
			rs.close();
			stmt.close();
		}
		catch (SQLException e)
		{
			throw new QueryException(e);
		}
		finally
		{
			closeConnection(conn);
		}
		
		return chs;
	}
	
	/**
	 * Updates the photo-url field of the given character, with the given URL.
	 * 
	 * @param id The ID of the character to update
	 * @param photoUrl The URL to set
	 * @throws QueryException
	 */
	public static void updateCharacterPhotoUrl(int id, String photoUrl) throws QueryException
	{
		Connection conn = openConnection();
		
		try
		{
			String sql = "UPDATE characters SET photo_url = ? WHERE id = ?;";
			PreparedStatement stmt = conn.prepareStatement(sql);
			
			stmt.setString(1, photoUrl);
			stmt.setInt(2, id);
			stmt.executeUpdate();
			stmt.close();
		}
		catch (SQLException e)
		{
			throw new QueryException(e);
		}
		finally
		{
			closeConnection(conn);
		}
	}
}
